﻿using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Data;
using System.Collections;

namespace DataBase
{
    /// <summary>
    /// PostgreSQL数据库操作类
    /// </summary>
    public class PostgreSqlHelp
    {
        #region//定义

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string _ConectionString = "";

        /// <summary>
        /// 数据库连接
        /// </summary>
        private NpgsqlConnection myConnection = new NpgsqlConnection();

        #endregion

        #region//属性集

        /// <summary>
        /// ConectionString 属性：设置/获取数据库连接字符串。
        /// </summary>
        public string ConectionString
        {
            get
            {
                return _ConectionString;
            }
            set
            {
                _ConectionString = value;
            }
        }

        #endregion

        #region//操作

        #region//Common

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public NpgsqlConnection OpenConnect()
        {
            try
            {
                if ((myConnection.State == ConnectionState.Broken) || (myConnection.State == ConnectionState.Closed))
                {
                    myConnection.ConnectionString = ConectionString;
                    myConnection.Open();
                }
                return myConnection;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnect()
        {
            try
            {
                if ((myConnection.State != ConnectionState.Broken) && (myConnection.State != ConnectionState.Closed))
                {
                    //myConnection.Close();
                    //myConnection.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 检验是否存在数据
        /// </summary>
        /// <returns></returns>
        public bool IfExist(string strSql)
        {
            OpenConnect();
            NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
            NpgsqlDataReader tmpDr = cmd.ExecuteReader();
            if (tmpDr.Read())
            {               
                return true;
            }
            else
            {               
                return false;
            }
        }

        #endregion

        #region//Execute

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public void ExecuteNonQuery(string strSql)
        {
            OpenConnect();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <param name="withTrans">是否启用事务</param>
        /// <returns></returns>
        public void ExecuteNonQuery(string strSql, bool withTrans)
        {
            OpenConnect();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                cmd.ExecuteNonQuery();
                if (withTrans == true)
                {
                    cmd.CommandText = "commit";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public void ExecuteSqlTrans(ArrayList strSqlList)
        {
            OpenConnect();
            NpgsqlCommand cmd = new NpgsqlCommand();
            cmd.Connection = myConnection;
            NpgsqlTransaction trans = myConnection.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                for (int n = 0; n < strSqlList.Count; n++)
                {
                    string strSql = strSqlList[n].ToString();
                    if (strSql.Trim().Length > 1)
                    {
                        cmd.CommandText = strSql;
                        cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
            }
            catch (NpgsqlException ex)
            {
                trans.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 执行多条SQL语句,实现数据库事务.
        /// </summary>
        /// <param name="strSqlList">每条语句以";"分割</param>
        public void ExecuteSqlTrans(string strSqlList)
        {
            OpenConnect();
            NpgsqlCommand cmd = new NpgsqlCommand();
            NpgsqlTransaction trans = myConnection.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                string[] split = strSqlList.Split(new Char[] { ';' });
                foreach (string strSql in split)
                {
                    if (strSql.Trim() != "")
                    {
                        cmd.CommandText = strSql;
                        cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
            }
            catch (NpgsqlException ex)
            {
                trans.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        #endregion

        #region//GetDataSet

        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string strSql)
        {
            OpenConnect();
            try
            {
                DataSet tmpDs = new DataSet();
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataAdapter myDataAdapter = new NpgsqlDataAdapter(cmd);
                myDataAdapter.Fill(tmpDs);
                return tmpDs;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        ///  根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        ///  </summary>
        ///  <param name="sql">查询语句</param>
        ///  <param name="sRecord">开始记录数</param>
        ///  <param name="mRecord">最大记录数</param>
        ///  <returns>DataSet</returns>       
        public DataSet GetDataSet(string strSql, int sRecord, int mRecord)
        {
            OpenConnect();
            try
            {
                DataSet tmpDs = new DataSet();
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataAdapter myDataAdapter = new NpgsqlDataAdapter(cmd);
                myDataAdapter.Fill(tmpDs, sRecord, mRecord, "tempTable");
                return tmpDs;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        #endregion

        #region//GetDataTable

        /// <summary>
        ///  获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string strSql)
        {
            OpenConnect();
            try
            {
                DataTable tmpDt = new DataTable();
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataAdapter myDataAdapter = new NpgsqlDataAdapter(cmd);
                myDataAdapter.Fill(tmpDt);
                return tmpDt;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 根据SQL查询返回DataTable对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sRecord">开始记录数</param>
        /// <param name="mRecord">最大记录数</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string strSql, int sRecord, int mRecord)
        {
            OpenConnect();
            try
            {
                DataTable tmpDt = new DataTable();
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataAdapter myDataAdapter = new NpgsqlDataAdapter(cmd);
                myDataAdapter.Fill(sRecord, mRecord, tmpDt);
                return tmpDt;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        #endregion

        #region//GetScalar

        /// <summary>
        /// 获得该SQL查询返回的第一行第一列的值，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回的第一行第一列的值</returns>
        public string GetScalar(string strSql)
        {
            string str = null;
            OpenConnect();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                str = cmd.ExecuteScalar().ToString();
                return str;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        #endregion

        #region//ColumnValues

        /// <summary>
        /// 返回SQL语句第一行,第ColumnI列
        /// </summary>
        /// <returns>字符串</returns>
        public string ColumnValues(string strSql, int ColumnI)
        {
            string result = "";
            OpenConnect();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataReader tmpDr = cmd.ExecuteReader();
                if (tmpDr.Read())
                {
                    result = tmpDr[ColumnI].ToString();
                }
                return result;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 返回SQL语句第一行,ColumnName列
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="ColumnI">列名</param>
        /// <returns></returns>
        public string ColumnValues(string strSql, string ColumnName)
        {
            string result = "";
            OpenConnect();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, myConnection);
                NpgsqlDataReader tmpDr = cmd.ExecuteReader();
                if (tmpDr.Read())
                {
                    result = tmpDr[ColumnName].ToString();
                }
                return result;
            }
            catch (NpgsqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnect();
            }
        }

        #endregion

        #endregion

    }
}
